-- @owner: zhao-pengcheng-g
-- @date: 2022/01/24
-- @testpoint:替代触发事件--INSTEAD OF

--step1:建表;expect:成功
drop table if exists table_src_0007 cascade;
CREATE TABLE table_src_0007(id1 INT, id2 INT, id3 INT) WITH (STORAGE_TYPE=USTORE)
partition by range(id2)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);

--step2：创建视图，函数，触发器，expect:成功
create or replace view test_trigger_src_view1_0007 as select * from table_src_0007;
CREATE OR REPLACE FUNCTION INSERT_FUNC_0007()
RETURNS TRIGGER AS $$
BEGIN
if(TG_OP='DELETE')
then
delete from table_src_0007 where id1=1;
RETURN NEW;
end if;
if(TG_OP='INSERT')
then
insert into table_src_0007 values(new.id1,new.id2,new.id3);
RETURN NEW;
end if;
if(TG_OP='UPDATE')
then
update table_src_0007 set id3=100;
RETURN NEW;
end if;
END;
$$LANGUAGE plpgsql;
/

drop trigger if exists INSERT_TRIGGER_0007 on test_trigger_src_view1_0007;
CREATE TRIGGER INSERT_TRIGGER_0007
INSTEAD OF delete or insert or update ON test_trigger_src_view1_0007
FOR EACH row
EXECUTE PROCEDURE INSERT_FUNC_0007();
/

--step3:执行DML操作，触发trigger，查询视图;expect:成功
insert into table_src_0007 values(1,1,1),(2,2,2),(3,3,3);
select * from test_trigger_src_view1_0007 order by id1;
delete from test_trigger_src_view1_0007 where id1=2;
select * from test_trigger_src_view1_0007 order by id1;
insert into test_trigger_src_view1_0007 values(4,4,4);
select * from test_trigger_src_view1_0007 order by id1;
update test_trigger_src_view1_0007 set id3=200;
select * from test_trigger_src_view1_0007 order by id1;

--step4:环境清理;expect:成功
drop TRIGGER INSERT_TRIGGER_0007 on test_trigger_src_view1_0007 cascade;
drop view test_trigger_src_view1_0007;
drop table if exists table_src_0007 cascade;
drop FUNCTION INSERT_FUNC_0007;

